﻿--9:50 P Sat/09/08/2008
--9:50 P Sat/09/08/2008
--9:50 P Sat/09/08/2008
/*
psql is the command line interface for PostgreSQL. psql enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file. In addition, it provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

After you type the command to connect to the user postgres form localhost,
you can create the database owner. means that you are connected to the database postgres and # means that the user you are connected to is a database superuser.
*/

--To create the database owner named softpedia we will use the CREATE ROLE command:

CREATE ROLE softpedia
login
password 'softpedia';

/*
'softpedia' is the password for the user softpedia. My advice is to change it and put what password you want. To verify if the user softpedia was successfully created, type 
softpedia
*/

--Next, we will learn to create the default tablespace, where the databaseӳ files will be stored. 
/*Create the folder c:/pgdata. Make sure the user posgres has full access to this folder. After that, create the folder: c:/pgdata/softpedia/system. Next step is to create softpedia_system tablespace:*/
CREATE TABLESPACE softpedia_system
 OWNER pagila
 LOCATION 'c:/pgdata/softpedia/system';

/*To check if softpedia_system tablespace was created, type db+ softpedia_system. Now you have to create the database and the database objects. To create softpedia database is very easy. If you know a few SQL commands itӳ piece of cake.*/

CREATE DATABASE softpedia
 OWNER softpedia
 TEMPLATE template0
 TABLESPACE softpedia_system;

/*Then verify if the database was created type: */
l+ 

/*The final step is to create the softpedia database objects. First connect to the database: c softpedia.
To create the database object make sure you have the sql script prepared and type: softpedia=# i c:/pgdata/softpedia-object.sql and then verify if the objects were imported successfully: softpedia=#d+ .*/


--======================================================
--======================================================
--======================================================
CREATE TABLE cities (
     name            varchar(80),
     location        point
 );
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO cities VALUES ('Ha Noi', '(19.0, 21.2)');


CREATE TABLE weather (
     city            varchar(80),
     temp_lo         int,           -- low temperature
     temp_hi         int,           -- high temperature
     prcp            real,          -- precipitation
     date            date
);
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');	 
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Ha Noi', 26, 39, 0.15, '2008-08-05');	 
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Ha Noi', 20, 26, 0.5, '2008-08-06');	 

SELECT * FROM weather;
/*
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)
*/

SELECT * FROM cities;
/*    name      | location  
 ---------------+-----------
  San Francisco | (-194,53)
(1 row)
*/
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
SELECT * FROM weather ORDER BY city;
SELECT * FROM weather ORDER BY city, temp_lo;
SELECT * FROM weather ORDER BY city, temp_lo, temp_hi;
SELECT DISTINCT city FROM weather;
SELECT DISTINCT city FROM weather ORDER BY city;


--2.6. Joins Between Tables
SELECT * FROM weather, cities WHERE city = name;
SELECT * FROM weather, cities WHERE weather.city=cities.name;
SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather, cities WHERE cities.name = weather.city;
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi;


--2.7. Aggregate Functions
SELECT max(temp_lo) FROM weather;
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
SELECT city, max(temp_lo) FROM weather GROUP BY city;
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%' GROUP BY city HAVING max(temp_lo) < 40;


--2.8. Updates
UPDATE weather SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2 WHERE date > '1994-11-28';



--2.9. Deletions
DELETE FROM weather WHERE city = 'Hayward';
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);


--3.2. Views
CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
/*
     city      | temp_lo | temp_hi | prcp |    date    | location  
---------------+---------+---------+------+------------+-----------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | (-194,53)
 Ha Noi        |      26 |      39 | 0.15 | 2008-08-05 | (19,21.2)
 Ha Noi        |      20 |      26 |  0.5 | 2008-08-06 | (19,21.2)
(4 rows)
*/


--3.3. Foreign Keys
drop view myview;--khong xoa view ko the xoa duoc cac table tao ra view
drop table cities;
drop table weather;

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO cities VALUES ('Ha Noi', '(19.0, 21.2)');

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');	 
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Ha Noi', 26, 39, 0.15, '2008-08-05');	 
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Ha Noi', 20, 26, 0.5, '2008-08-06');	 


INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
--KQ: ERR:
--ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
--DETAIL:  Key (city)=(Berkeley) is not present in table "cities".
insert into cities values('Berkeley', '(1.05, 2.36)');
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');--RunningOK


--3.4. Transactions
/*
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. 

For example, consider a bank database that contains balances for various customer accounts, as well as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from Alice's account to Bob's account. Simplifying outrageously, the SQL commands for this might look like: 

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');



It's possible to control the statements in a transaction in a more granular fashion through the use of savepoints. Savepoints allow you to selectively discard parts of the transaction, while committing the rest. After defining a savepoint with SAVEPOINT, you can if needed roll back to the savepoint with ROLLBACK TO. All the transaction's database changes between defining the savepoint and rolling back to it are discarded, but changes earlier than the savepoint are kept. 

After rolling back to a savepoint, it continues to be defined, so you can roll back to it several times. Conversely, if you are sure you won't need to roll back to a particular savepoint again, it can be released, so the system can free some resources. Keep in mind that either releasing or rolling back to a savepoint will automatically release all savepoints that were defined after it. 

All this is happening within the transaction block, so none of it is visible to other database sessions. When and if you commit the transaction block, the committed actions become visible as a unit to other sessions, while the rolled-back actions never become visible at all. 

Remembering the bank database, suppose we debit $100.00 from Alice's account, and credit Bob's account, only to find later that we should have credited Wally's account. We could do it using savepoints like this: 

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;	
	
*/
--3.5. Inheritance
/*Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design. */
drop view myview;
drop table weather;
drop table cities;--Muon xoa duoc table phai xoa het nhung rang buoc: cac view lien quan, cac table rang buoc, ...
drop view myview;
drop table weather;
drop table cities;

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);
insert into cities values('Ha Noi', 6.24, 50);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

--BACKUP DATABASE:
--Sao luu 1 database;
#pg_dump -U <Username> -d <databasename> -h <hostname> > <databasename>.dmp
pg_dump -U sa -d mydb -h localhost > mydb.backup
--Sao luu tat ca database;
pg_dumpall -U postgresql -D > D:\workspace\project2\dbALL.backup


--RESTORE DATABASE:
--Phuc hoi 1 datbase;
createddb <db name>
psql -U <Username> -h <Hostname> -d <db name> < <db backuped.dmp>

drop database <DB Name>;
pg_restore.exe -h localhost -p 5432 -U sa -d mydb -l "D:\workspace\JavaProject\project2\db\mydb20080829.backup"

--Phuc hoi tat ca database;
psql -U <username> -h <Host name> < <all.dmp>

--11:08 AM Wed/13/08/2008
--There is a limit on how many columns a table can contain. 
--*****Depending on the column types, it is between 250 and 1600

--8:22 AM Fri/29/08/2008
--Add a new column with check option
CREATE TABLE employee (--@@1
     ID         int,
     name       varchar(10),
     salary     real,
     start_date date,
     city       varchar(10),
     region     char(1)
 );
insert into employee (ID, name, salary, start_date, city, region) values (1, 'Jason', 40420,  '02/01/94', 'New York', 'W');
insert into employee (ID, name, salary, start_date, city, region) values (2, 'Robert', 14420, '01/02/95', 'Vancouver','N');
insert into employee (ID, name, salary, start_date, city, region) values (3, 'Celia', 24020,  '12/03/96', 'Toronto', 'W');
insert into employee (ID, name, salary, start_date, city, region) values (4, 'Linda', 40620,  '11/04/97', 'New York', 'N');
insert into employee (ID, name, salary, start_date, city, region) values (5, 'David', 80026,  '10/05/98', 'Vancouver','W');
insert into employee (ID, name, salary, start_date, city, region) values (6, 'James', 70060,  '09/06/99', 'Toronto', 'N');
insert into employee (ID, name, salary, start_date, city, region) values (7, 'Alison', 90620, '08/07/00', 'New York', 'W');
insert into employee (ID, name, salary, start_date, city, region) values (8, 'Chris', 26020,  '07/08/01', 'Vancouver','N');
insert into employee (ID, name, salary, start_date, city, region) values (9, 'Mary', 60020,   '06/09/02', 'Toronto', 'W');
select * from employee;

--ALTER TABLE: "ADD A COMLUMN"
ALTER TABLE employee ADD COLUMN description text CHECK (description <> '');
\d employee;
drop table employee;




--ALTER TABLE: "ADD A COMLUMN"
CREATE TABLE books (--@@2
	  id integer UNIQUE,
	  title text NOT NULL,
	  author_id  integer,
	  subject_id integer,
	  CONSTRAINT books_id_pkey PRIMARY KEY (id)
);
\d books;

--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books"
ALTER TABLE books ADD publication date;--Them 1 column;
\d books;--xem thong tin. 1 cot da duoc them.	
drop table books;


--Altering column defaults
CREATE TABLE books (
	  id integer UNIQUE,
	  title text NOT NULL,
	  author_id  integer,
	  subject_id integer,
	  CONSTRAINT books_id_pkey PRIMARY KEY (id)
);
-- Altering column defaults
ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval('book_ids');--ERROR:  relation "book_ids" does not exist
drop table books;


--Adds a comment to the customers table
\d employee;
select * from employee;
COMMENT ON TABLE employee IS 'For employee names.';-- Adds a comment to the customers table:


--Changing a Column's Data Type
\d employee;
select * from employee;
ALTER TABLE employee ALTER COLUMN salary TYPE numeric(10, 2);-- Changing a Column's Data Type


--Reference column type through column type
\d employee;
select * from employee;

--Creating FUNCTION: //Chi dung duoc voi "pgADMIN III"
CREATE FUNCTION "get_employee" (text) RETURNS text AS '
	DECLARE
	   ecity ALIAS FOR $1;
	   ename employee.name%TYPE;	
	BEGIN
	   SELECT INTO ename name FROM employee WHERE city = ecity;
	   return ecity || '' '' || ename;
	END;
	' LANGUAGE 'plpgsql';

select get_employee(city) from employee;
drop function get_employee(text);
\d employee;
drop table employee;


--Reference column name through table name
CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);--Insert value to composite column
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 0.99;--To access a field of a composite column
drop table on_hand;
drop type inventory_item;


--Creates a backup table
CREATE TABLE employee_backup AS SELECT * FROM employee;
select * from employee_backup;
drop table employee_backup;



-- Copy the employees table to the emp_table file, using a pipe (|) as the field delimiter
select * from employee;
COPY employee TO 'D:\\workspace\\project2\\db\\employee_data.data' USING DELIMITERS '|';

-- Copies data from a system file into the publishers table:
COPY employee FROM 'D:\\workspace\\project2\\db\\employee_data.data';
drop table employee;



--Copying an ASCII file to data table
drop table employee;
CREATE TABLE employee (
    ID         int,
    name       varchar(10)
);
/*
file: subjects.data
1,Joe
2,Alison
3,Jess
*/
-- Copying an ASCII file, nen de duong dan don gian, ngan gon. Duong dan dai hay gay ra loi;
COPY subjects FROM 'D:\\workspace\\project2\\db\\subjects.sql' USING DELIMITERS ',';-- WITH NULL AS '\null';
--Nen dung: 
COPY subjects FROM 'D:/workspace/project2/db/subjects.sql' USING DELIMITERS ',';



-- Removing a Column
drop table employee;--Dung table tai dong @@1
\d employee;
ALTER TABLE employee DROP COLUMN city;
\d employee;

drop table employee;--Dung table tai dong @@1
ALTER TABLE employee DROP COLUMN city CASCADE



--Renaming a Column
drop table employee;--Dung table tai dong @@1
ALTER TABLE employee RENAME COLUMN id TO No;

--ALTER TABLE books RENAME COLUMN
drop table books;-- @@2
ALTER TABLE books RENAME COLUMN id TO table_id;
ALTER TABLE books RENAME table_id TO id;

--Rename a column
drop table employee;--Dung table tai dong @@1
ALTER TABLE employee RENAME COLUMN start_date TO hire_date;


--Renaming a table
drop table books;-- @@2
ALTER TABLE books RENAME TO literature;


--Use \d to display table information
drop table books;-- @@2
drop table literature;
CREATE TABLE new_books (
	id, 
	title, 
	author_id, 
	subject_id
) 
AS SELECT id, title, author_id, subject_id FROM books;
ALTER TABLE books RENAME TO old_books;
ALTER TABLE new_books RENAME TO books;



--Changing table ownership
drop table employee;--@@1
ALTER TABLE employee OWNER TO postgres;-- Changing table ownership



--Grants all privileges on a table to the user manager
drop table employee;--@@1
/*
                   List of relations
 Schema |           Name            | Type  |  Owner   
--------+---------------------------+-------+---------- 
 public | employee                  | table | postgres
 */
 create user manager;
 GRANT ALL ON employee TO manager;
 
 -- Use the \z psql command to view access privileges on the publishers table:
\z employee;
drop table employee;
drop user manager;



--Copying the books table to an ASCII file
drop table employee;--@@1
COPY employee TO 'D:/employee.data';

--Copy the employees table to the emp_table file: using a pipe (|) as the field delimiter
COPY employee TO 'D:/workspace/project2/db/employee.data' USING DELIMITERS '\t';


--Create a temporary table
drop table employee;--@@1
SELECT * INTO TEMP TABLE old_emp FROM employee WHERE id < 5;

drop table old_emp;

--TRUNCATE TABLE:
drop table employee;--@@1

TRUNCATE TABLE employee;



--DELETE ONLY
--DELETE ONLY from parent table
CREATE TABLE "authors" (
      "id" integer NOT NULL,
      "last_name" text,
      "first_name" text,
      Constraint "authors_pkey" Primary Key ("id")
);
select * from authors;

CREATE TABLE distinguished_authors (--inheritance from authors
	award text
) INHERITS (authors);
INSERT INTO distinguished_authors VALUES (1809, 'G', 'T', 'Prize');
SELECT * FROM authors;

DELETE FROM ONLY authors WHERE last_name = 'G';--Chang xoa duoc dong nao
--KL: Khong xoa duoc du lieu o bang CHA.
SELECT * FROM authors;
SELECT * FROM distinguished_authors;



--INSERT INTO vv SELECT 'abc' || 'def'
CREATE TABLE vv (
	v character(20)
);
INSERT INTO vv SELECT 'abc' || 'def';


--UPDATE--
--UPDATE employee SET name = 'O\'Reilly', city = 'HATAY' || '-HN' WHERE id = 2;

create table job(
     ID          int,
     title       varchar (10)
);
insert into job(ID, title) values(1,'Developer');
insert into job(ID, title) values(2,'Tester');
insert into job(ID, title) values(3,'Designer');
insert into job(ID, title) values(4,'Programmer');
select * from job;
UPDATE employee SET salary = salary + 100 FROM job WHERE job.id = employee.id;



--Update in a slice
CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
INSERT INTO sal_emp VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]
);
INSERT INTO sal_emp VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
	ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]
);
SELECT * FROM sal_emp;
/*
 name  |      pay_by_quarter       |                 schedule                  
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
*/
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol';

--http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm

--11:19 P Fri/29/08/2008
--COMMENT ON DATABASE
 -- Create Database "bookdown";

create database bookdown;
COMMENT ON DATABASE "bookdown" IS 'The Book Town Database.';
drop database bookdown;


--The NATURAL and USING clauses
drop table books;
CREATE TABLE "books" (
      "id"           integer NOT NULL,
      "title"        text NOT NULL,
      "author_id"    integer,
      "subject_id"   integer,
      Constraint "books_id_pkey" Primary Key ("id")
);
insert into books values (7808,  'Java', 				4156, 9);
insert into books values (4513,  'Javascript', 			1866, 15);
insert into books values (4267,  'C#',  				2001, 15);
insert into books values (1608,  'Oracle',  			1809, 2);
insert into books values (1590,  'Sql Server',  			1809, 2);
insert into books values (25908, 'Postgre SQL',  		15990, 2);
insert into books values (1501,  'Python',  			2031, 2);
insert into books values (190,   'Java by API', 			16, 6);
insert into books values (1234,  '2D', 					25041, 3);
insert into books values (2038,  'C', 					1644, 0);
insert into books values (156,   'C++',  				115, 9);
insert into books values (41473, 'Programming Python',  	7805, 4);
insert into books values (41477, 'Learning Python',   	7805, 4);
insert into books values (41478, 'Perl Cookbook',        7806, 4);
insert into books values (41472, 'Practical PostgreSQL',  1212, 4);
select * from books;


CREATE TABLE "authors" (
      "id" integer NOT NULL,
      "last_name" text,
      "first_name" text,
      Constraint "authors_pkey" Primary Key ("id")
);
insert into authors values (1111,  'Martin',       'Jason');
insert into authors values (1212,  'Worsley',      'Robert');
insert into authors values (15990, 'Mathews',      'John');
insert into authors values (25041, 'Smith',       'Williams');
insert into authors values (16,    'Alcott',       'May');
insert into authors values (4156,  'King',         'Stephen');
insert into authors values (1866,  'Herbert',      'Margaret');
insert into authors values (1644,  'Hogarth',      'Celia');
insert into authors values (2031,  'Brown',        'Wise');
insert into authors values (115,   'Poe',          'Allen');
insert into authors values (7805,  'Lutz',         'Mark');
insert into authors values (7806,  'Rice',         'Tom');
insert into authors values (1533,  'Black',        'Chris');
insert into authors values (1717,  'Brite',        'Linda');
insert into authors values (2112,  'Larry',        'Edward');
insert into authors values (2001,  'Clarke',       'Alison');

--NATURAL
SELECT title, last_name, first_name 
FROM books INNER JOIN authors AS a (author_id) USING (author_id) 
WHERE last_name = 'Lutz';

SELECT title, last_name, first_name
FROM books NATURAL INNER JOIN authors AS a (author_id)
WHERE last_name = 'Lutz';



--Close a cursor
CREATE TABLE "publishers" (--@@3
	"id" integer NOT NULL,
	"name" text,
	"address" text,
	Constraint "publishers_pkey" Primary Key ("id")
);
insert into publishers values(150, 'Can Press',  '29 Ave. Toronto, ON');
insert into publishers values(91,  'Henry Inc.', '115 Street New York, NY');
insert into publishers values(113, 'O Inc.',     '101 St, Sebastopol, CA');
insert into publishers values(62,  'W Pubs',     '1515, New York');
insert into publishers values(105, 'N Press',    '19 W, New York');
insert into publishers values(99,  'A Inc',      '375 St, New York');
insert into publishers values(101, 'Z Inc',      '375 Ave, New York');
insert into publishers values(163, 'M Press',    'PO Box 1215');
insert into publishers values(171, 'B',          '16 W. 18th St. New York');
insert into publishers values(102, 'P Inc',      '375 Hudson St, New York');
insert into publishers values(75,  'D Inc',      '1540 Broadway, New York');
insert into publishers values(65,  'H Pubs',     '10 E 53rd St, New York');
insert into publishers values(59,  'R Inc',      '1540 Broadway, New York');
select * from publishers;

-- Declares a cursor named cur_publisher and then uses that cursor to fetch 2 rows.
BEGIN WORK;--Bat dau
DECLARE cur_publisher CURSOR FOR SELECT name FROM publishers;--Khai bao 1 con tro
FETCH FORWARD 2 IN cur_publisher;--cho chay 2 record 1 lan
close cur_publisher;--Dong con tro lai
END WORK;--Ket thuc cong viec


--http://www.java2s.com/Code/PostgreSQL/Cursor/Opensatransactiondeclaresthecurpublisherscursorandclosesit.htm
--Using cursor in transaction
drop table employee;--@@1
BEGIN;
	DECLARE cur_employee CURSOR FOR SELECT id, name FROM employee;
	FETCH FORWARD 2 IN cur_employee;

	-- Fetch backwards in the cur_employee cursor:
	FETCH BACKWARD 3 IN cur_employee;
END;



--reate a simple function

CREATE FUNCTION "title" (integer)
RETURNS text AS
	'SELECT name from employee where id = $1'
LANGUAGE 'sql';

select * from title(1);

--5:27 P Mon/01/09/2008
--p91/402: McGraw.Hill.PostgreSQL.8.for.Windows.Mar.2007.pdf
-- Database: test
-- DROP DATABASE test;
CREATE DATABASE test
  WITH OWNER = postgres
       ENCODING = 'UTF8';
COMMENT ON DATABASE test IS 'DB APPLICATION';


--8:02 P Fri/05/09/2008
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -b -D -v -f "D:\workspace\JavaProject\project2\db\mydb.backup" mydb
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -b -D -v -f "D:\workspace\JavaProject\project2\db\test.backup" test


--2008.0906 9h15
CREATE ROLE "SYSTEMADMIN"
  SUPERUSER CREATEDB CREATEROLE
   VALID UNTIL 'infinity';
UPDATE pg_authid SET rolcatupdate=true WHERE rolname='SYSTEMADMIN';

-- Role: "SYSTEMADMIN"
-- DROP ROLE "SYSTEMADMIN";
CREATE ROLE "SYSTEMADMIN"
  SUPERUSER INHERIT CREATEDB NOCREATEROLE;
UPDATE pg_authid SET rolcatupdate=true WHERE OID=18289::oid;

--Created user inherited from ROLE 'SYSTEM '
CREATE ROLE sa LOGIN ENCRYPTED PASSWORD 'md55de6d65b641934d89e03f66f428681b6'
  SUPERUSER CREATEDB CREATEROLE
   VALID UNTIL 'infinity'
   IN ROLE "SYSTEMADMIN";
UPDATE pg_authid SET rolcatupdate=true WHERE rolname='sa';


--10:17 AM 06/09/2008

CREATE ROLE "SYSTEMADMIN"
  SUPERUSER CREATEDB CREATEROLE
   VALID UNTIL 'infinity';
COMMENT ON ROLE "SYSTEMADMIN" IS 'SYSTEM ADMIN';

CREATE ROLE sa LOGIN ENCRYPTED PASSWORD 'md55de6d65b641934d89e03f66f428681b6'
  SUPERUSER CREATEDB CREATEROLE
   VALID UNTIL 'infinity';
COMMENT ON ROLE sa IS 'SA';

--
CREATE DATABASE mydb
  WITH ENCODING='UTF8'
       OWNER=sa
       TEMPLATE=template0
       TABLESPACE=pg_default;

--p114/402
CREATE TABLE "Customer"
(
   "CustomerID" character(6) NOT NULL, 
   "LastName" character(50), 
   "FirstName" character(50), 
   "Address" character(254), 
   "City" character(50), 
   "State" character(2), 
   "Zip" character(5), 
   "Phone" character(20), 
   CONSTRAINT "Customer_PK" PRIMARY KEY ("CustomerID") USING INDEX TABLESPACE pg_default
) WITH (OIDS=TRUE)
TABLESPACE pg_default;

ALTER TABLE "Customer" OWNER TO sa;

--
CREATE TABLE "Product"
(
   "ProductID" character(6) NOT NULL, 
   "ProductName" character(254), 
   "Model" character(100), 
   "Manufacturer" character(254), 
   "UnitPrice" money, 
   "Inventory" integer, 
   CONSTRAINT "Product_PK" PRIMARY KEY ("ProductID") USING INDEX TABLESPACE pg_default
) WITH (OIDS=TRUE)
;
ALTER TABLE "Product" OWNER TO sa;

--
CREATE TABLE "Order"
(
   "OrderID" character(6) NOT NULL, 
   "CustomerID" character(6) NOT NULL, 
   "ProductID" character(6) NOT NULL, 
   "PurchaseDate" date, 
   "Quantity" integer, 
   "TotalCost" money, 
   CONSTRAINT "Order_PK" PRIMARY KEY ("OrderID") USING INDEX TABLESPACE pg_default, 
   CONSTRAINT "Order_FK1" FOREIGN KEY ("CustomerID") REFERENCES "Customer" ("CustomerID")    ON UPDATE NO ACTION ON DELETE NO ACTION, 
   CONSTRAINT "Order_FK2" FOREIGN KEY ("ProductID") REFERENCES "Product" ("ProductID")    ON UPDATE NO ACTION ON DELETE NO ACTION
) WITH (OIDS=TRUE)

TABLESPACE pg_default;
ALTER TABLE "Order" OWNER TO sa;
COMMENT ON TABLE "Order" IS 'Order table=Customer + Product';

select * from Customer;--ERROR: relation "customer" does not exist 
--SQL state: 42P01

select * from books;--OK


--p120/402
--11:33 P Sat/06/09/2008
ALTER TABLE store."Customers" DROP CONSTRAINT "CustomerKey";
ALTER TABLE store."Customers" ADD CONSTRAINT "Customer_PK" PRIMARY KEY ("CustomerID") USING INDEX TABLESPACE pg_default;

ALTER TABLE store."Customers" OWNER TO postgres;
COMMENT ON TABLE store."Customers" IS ' Customer table';

insert into store.Customer values ('BLUM001', 'Blum', 'Rich', '123 Main Str.', 'Gary', 'IN', 46100, '555-1234');




--*********************
--7:23 A Sun/07/09/2008
-- Table: store."Customer"

-- DROP TABLE store."Customer";

CREATE TABLE store."Customer"
(
  "CustomerID" character(6) NOT NULL, -- Unique identifier for each user
  "LastName" text, -- Last name of customer
  "FirstName" text, -- First name of customer
  "Address" text, -- Street address of customer
  "City" text,
  "State" character(2),
  "Zip" character(5),
  "Phone" text,
  CONSTRAINT "Customer_PK" PRIMARY KEY ("CustomerID")
)
WITH (OIDS=FALSE);
ALTER TABLE store."Customer" OWNER TO sa;
COMMENT ON TABLE store."Customer" IS 'Customer table';
COMMENT ON COLUMN store."Customer"."CustomerID" IS 'Unique identifier for each user
';
COMMENT ON COLUMN store."Customer"."LastName" IS 'Last name of customer';
COMMENT ON COLUMN store."Customer"."FirstName" IS 'First name of customer';
COMMENT ON COLUMN store."Customer"."Address" IS 'Street address of customer';



-- Table: store."Product"
-- DROP TABLE store."Product";
CREATE TABLE store."Product"
(
  "ProductID" character(6) NOT NULL, -- Unique primary key identifier that is not NULL
  "ProductName" text, -- Name of product
  "Model" text,
  "Manufacture" text,
  "UnitPrice" money,
  "Inventory" integer, -- Number of unit in inventory
  CONSTRAINT "Product_PK" PRIMARY KEY ("ProductID")
)
WITH (OIDS=FALSE);
ALTER TABLE store."Product" OWNER TO sa;
COMMENT ON TABLE store."Product" IS 'Product table';
COMMENT ON COLUMN store."Product"."ProductID" IS 'Unique primary key identifier that is not NULL';
COMMENT ON COLUMN store."Product"."ProductName" IS 'Name of product';
COMMENT ON COLUMN store."Product"."Inventory" IS 'Number of unit in inventory';


-- Table: store."Order"
-- DROP TABLE store."Order";
CREATE TABLE store."Order"
(
  "OrderID" character(6) NOT NULL, -- Unique PK iden tifier that is not NULL
  "CustomerID" character(6), -- The CustomerID from the Customer table (not NULL)
  "ProductID" character(6), -- The ProductID from the Product table (not NULL)
  "PurchaseDate" date, -- Date of purchase
  "Quantity" integer, -- The number of items purchased
  "TotalCost" money, -- The total cose of the purchase
  CONSTRAINT "Order_PK" PRIMARY KEY ("OrderID"),
  CONSTRAINT "Order_Customer_FK1" FOREIGN KEY ("CustomerID")
      REFERENCES store."Customer" ("CustomerID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "Order_Product_FK2" FOREIGN KEY ("ProductID")
      REFERENCES store."Product" ("ProductID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE store."Order" OWNER TO sa;
COMMENT ON TABLE store."Order" IS 'Order table';
COMMENT ON COLUMN store."Order"."OrderID" IS 'Unique PK iden tifier that is not NULL';
COMMENT ON COLUMN store."Order"."CustomerID" IS 'The CustomerID from the Customer table (not NULL)';
COMMENT ON COLUMN store."Order"."ProductID" IS 'The ProductID from the Product table (not NULL)';
COMMENT ON COLUMN store."Order"."PurchaseDate" IS 'Date of purchase';
COMMENT ON COLUMN store."Order"."Quantity" IS 'The number of items purchased';
COMMENT ON COLUMN store."Order"."TotalCost" IS 'The total cose of the purchase';


insert into store."Customer" values('BLU001', 'Blum', 'Rich', '123 Main Str.', 'Gary', 'IN', 46100, '555-1234');
insert into store."Customer" values('WIL001', 'Williams', 'Frank', '456 Oak Str.', 'Hammond', 'IN', 46102, '555-9876');
select * from store."Customer";

--p120/402
insert into store."Customer" values('BLU002', 'Blum', 'Barbara', '789 Pine Ave.', 'Whiting', 'IN', 46106, '555-9876');



--2:38 A Mon/08/09/2008
--Tao ROLE de phan quyen truy cap cac table;
-- Role: "Salesman"
-- DROP ROLE "Salesman";
CREATE ROLE "Salesman"
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

-- Role: "Accountant"
-- DROP ROLE "Accountant";
CREATE ROLE "Accountant"
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

--store."Customer";
GRANT ALL ON TABLE store."Customer" TO sa;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Customer" TO "Salesman";
GRANT SELECT ON TABLE store."Customer" TO "Accountant";

--store."Order"
GRANT ALL ON TABLE store."Order" TO sa;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Order" TO "Salesman";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Order" TO "Accountant";

--store."Product"
GRANT ALL ON TABLE store."Product" TO sa;
GRANT SELECT ON TABLE store."Product" TO "Salesman";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Product" TO "Accountant";


--Sau khi tao ROLE cho cac tanle xong, xem thong tin cac table:
-- Table: store."Customer"
-- DROP TABLE store."Customer";
CREATE TABLE store."Customer"
(
  "CustomerID" character(6) NOT NULL, -- Unique identifier for each user
  "LastName" text, -- Last name of customer
  "FirstName" text, -- First name of customer
  "Address" text, -- Street address of customer
  "City" text,
  "State" character(2),
  "Zip" character(5),
  "Phone" text,
  CONSTRAINT "Customer_PK" PRIMARY KEY ("CustomerID")
)
WITH (OIDS=FALSE);
ALTER TABLE store."Customer" OWNER TO sa;
GRANT ALL ON TABLE store."Customer" TO sa;--
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Customer" TO "Salesman";--
GRANT SELECT ON TABLE store."Customer" TO "Accountant";--
COMMENT ON TABLE store."Customer" IS 'Customer table';
COMMENT ON COLUMN store."Customer"."CustomerID" IS 'Unique identifier for each user';
COMMENT ON COLUMN store."Customer"."LastName" IS 'Last name of customer';
COMMENT ON COLUMN store."Customer"."FirstName" IS 'First name of customer';
COMMENT ON COLUMN store."Customer"."Address" IS 'Street address of customer';


-- Table: store."Order"
-- DROP TABLE store."Order";
CREATE TABLE store."Order"
(
  "OrderID" character(6) NOT NULL, -- Unique PK iden tifier that is not NULL
  "CustomerID" character(6), -- The CustomerID from the Customer table (not NULL)
  "ProductID" character(6), -- The ProductID from the Product table (not NULL)
  "PurchaseDate" date, -- Date of purchase
  "Quantity" integer, -- The number of items purchased
  "TotalCost" money, -- The total cose of the purchase
  CONSTRAINT "Order_PK" PRIMARY KEY ("OrderID"),
  CONSTRAINT "Order_Customer_FK1" FOREIGN KEY ("CustomerID")
      REFERENCES store."Customer" ("CustomerID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "Order_Product_FK2" FOREIGN KEY ("ProductID")
      REFERENCES store."Product" ("ProductID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE store."Order" OWNER TO sa;
GRANT ALL ON TABLE store."Order" TO sa;--
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Order" TO "Salesman";--
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Order" TO "Accountant";--
COMMENT ON TABLE store."Order" IS 'Order table';
COMMENT ON COLUMN store."Order"."OrderID" IS 'Unique PK iden tifier that is not NULL';
COMMENT ON COLUMN store."Order"."CustomerID" IS 'The CustomerID from the Customer table (not NULL)';
COMMENT ON COLUMN store."Order"."ProductID" IS 'The ProductID from the Product table (not NULL)';
COMMENT ON COLUMN store."Order"."PurchaseDate" IS 'Date of purchase';
COMMENT ON COLUMN store."Order"."Quantity" IS 'The number of items purchased';
COMMENT ON COLUMN store."Order"."TotalCost" IS 'The total cose of the purchase';

-- Table: store."Product"
-- DROP TABLE store."Product";
CREATE TABLE store."Product"
(
  "ProductID" character(6) NOT NULL, -- Unique primary key identifier that is not NULL
  "ProductName" text, -- Name of product
  "Model" text,
  "Manufacture" text,
  "UnitPrice" money,
  "Inventory" integer, -- Number of unit in inventory
  CONSTRAINT "Product_PK" PRIMARY KEY ("ProductID")
)
WITH (OIDS=FALSE);
ALTER TABLE store."Product" OWNER TO sa;
GRANT ALL ON TABLE store."Product" TO sa;--
GRANT SELECT ON TABLE store."Product" TO "Salesman";--
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE store."Product" TO "Accountant";--
COMMENT ON TABLE store."Product" IS 'Product table';
COMMENT ON COLUMN store."Product"."ProductID" IS 'Unique primary key identifier that is not NULL';
COMMENT ON COLUMN store."Product"."ProductName" IS 'Name of product';
COMMENT ON COLUMN store."Product"."Inventory" IS 'Number of unit in inventory';


--Van chua xong vi chua gan quyen cua group len schema "store"
GRANT USAGE ON SCHEMA store TO "Accountant";
GRANT USAGE ON SCHEMA store TO "Salesman";

--Xem quyen tren schema "STORE"
-- Schema: "store"
-- DROP SCHEMA store;
CREATE SCHEMA store
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA store TO postgres;
GRANT USAGE ON SCHEMA store TO "Accountant";
GRANT USAGE ON SCHEMA store TO "Salesman";
GRANT ALL ON SCHEMA store TO sa WITH GRANT OPTION;
COMMENT ON SCHEMA store IS 'FIRST SCHEMA';


--Sau khi tao group role "Salesman", "Accountant", chung ta se tao user thuoc 2 group role nay, goi la "Login Roles";
--p127/402
-- Role: "fred"
-- DROP ROLE fred;
CREATE ROLE fred LOGIN
  ENCRYPTED PASSWORD 'md574cc3de9b8b91aa198f929e4c4955b2c'--123456
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT "Salesman" TO fred;
COMMENT ON ROLE fred IS 'Salesman group role';

--change pass;
ALTER Role fred ENCRYPTED PASSWORD 'md56d49822a4a97b7afed9fe9d21c7005f5';--654321

--change group roles;
GRANT "Accountant" TO fred;--
REVOKE "Salesman" FROM fred;

--sau khi change group roles, change pass;
-- Role: "fred"
-- DROP ROLE fred;
CREATE ROLE fred LOGIN
  ENCRYPTED PASSWORD '654321'--654321
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT "Accountant" TO fred;--
COMMENT ON ROLE fred IS 'Salesman group roles';


-- Role: "barney"
-- DROP ROLE barney;
CREATE ROLE barney LOGIN
  ENCRYPTED PASSWORD '654321'--123456
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT "Salesman" TO barney;
COMMENT ON ROLE barney IS 'Salesman group roles';


--Sau khi tao xong "Group Roles" va "Login Roles", test;
user/pass: fred/654321, Accountant
user/pass: barney/123456, Salesman

C1: Login vao "pgAdmin III" voi user/pass da cho va kiem tra

c2: Dung cau lenh:
#psql <db name> <username>
#psql test fred
pass: 654321
\d store.*;--Nhin chi tiet thong tin ve 3 table Customer, Order, Product;
--Fred la user (Login Roles) thuoc group (Group Roles) "Accountant";
insert into store."Product" values ('LAP001', 'Laptop', 'TakeAlong', 'Acme', '500.00', 100);--INSERT thanh cong;

-- Tat nhien la ko thuoc Group Roles Customer-->Khong the ghi du lieu len table store."Customer";
test=> insert into store."Customer" values ('CXT001', 'Cao Xuan', 'Tinh', '357 Tam Tinh', 'HANOI', 'HM', '844', '6330769');
ERROR:  permission denied for relation Customer
test=> 
--KL: Da kiem tra xong, hoan toan hop le voi yeu cau dat ra:
Group Roles: 	Accountant       		Salesman
User Roles:  	fred/654321				barney/123456
Write access: 	Product, Order			Customer, Order
Read access:  	Customer				Product
Kiem tra tuong tu voi user barney/123456

--BACKUP DB:
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -b -D -v -f "D:\workspace\JavaProject\project2\db\test.backup" test



--*********
--DATABASE MAINTENANCE
--p130/402
/*
Trong 1 table, khi delete/update, những khoảng trống "chết" đc tạo ra và chiếm không gian của table. Đây là kết quả của viẹc delete/update các record. Những record được đánh dấu deleted chứ không hoàn toàn bị xoá theo nghĩa vật lý. (Cũng giống như bạn xoá dữ liệu trên máy tính vậy, dù bị xoá đi nhưng vẫn có công cụ lấy lại những dữ liệu đã xoá).
Và nhớ rằng, bất cứ khi nào, 1 transaction có thể "ROLLLED BACK". Nghĩa là những dữ liệu đã xoá trước đây sẽ đc phục hồi lại.
Theo thời gian, 1 table có thể chứa nhiều record bị xoá hơn lại dữ liệu thật của nó, và như vậy dung lượng table sẽ tăng lên đáng kể vì nó không tự giải phóng những record đã "xoá".
Vì vậy POSTGRESQL cung cấp 1 method gọi là "vacuuming" để xoá đi những record đc đánh dấu xoá.

Chương 3 đã chỉ ra file [postgresql.conf] có thể cấu hình autovacuuming [vacuum_naptime]

Vacuum có thể đc cấu hình qua pgAdmin III, có vài cách để vacuum record từ table:
1. Set 1 table tự động vacuum
2. Set bằng tay 1 table nào đó.
3. Set bằng tay cho tất cả các table trong database.
-Right click on table, chọn [Properties...]
-Select [Vacuum Settíng] tab
-The top check allowed you modify the vacuum config parameter

Có thể set vacuum cho database:
1. Right vào database name
2. Chọn [Maintenance...]


Kết luận:
---------
Maintain table/database cho ta khả năng:
1. [x] Vacuum the table
2. [x] Analyze the table to determine if vacuuming is necessery
3. [x] ReIndex all indexes for the table.

Tác dụng: 
Xoá dung lượng bộ nhớ vật lý chưa đc giải phóng
Tăng tốc độ query cho table vì table đc reIndex
*/


--BACKUPS & RESTORES
--p132/402
--11:52 P Wed/10/09/2008
/*catastrophic /,kætə'strɔfik/*  tính từ
- thảm hoạ, thê thảm*/
#pg_dumpall cho phép backup toàn bộ database POSTGRESQL, bao gồm LOGIN và GROUP ROLES
#pg_dump cho phép backup từng databse riêng lẻ.

--8:15 P Fri/12/09/2008
/*pg_restore để phục hồi toàn bộ, nếu dùng PgAdminIII, để phục hồi database, schema, table, ta phải tạo ra "bộ khung xương" cho nó bằng cách tạo 1 database, schema, table rỗng rồi từ đó restore*/

--Backup globals;
D:\Program Files\PostgreSQL\8.3\bin\pg_dumpall.exe -h localhost -p 5432 -U sa -v -f "D:\workspace\JavaProject\project2\db\backup-globals.backup" --globals-only
/*
pg_dumpall: executing SET search_path = pg_catalog
pg_dumpall: executing SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'Accountant'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'SA'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'Salesman'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'USER'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'barney'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'fred'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'postgres'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'sa'
pg_dumpall: executing SELECT rolconfig[1] FROM pg_authid WHERE rolname = 'user1'
pg_dumpall: executing SELECT ur.rolname AS roleid, um.rolname AS member, a.admin_option, ug.rolname AS grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor ORDER BY 1,2,3
pg_dumpall: executing SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, spcacl, pg_catalog.shobj_description(oid, 'pg_tablespace') FROM pg_catalog.pg_tablespace WHERE spcname !~ '^pg_' ORDER BY 1

Process returned exit code 0.
*/
--Backup servers;
D:\Program Files\PostgreSQL\8.3\bin\pg_dumpall.exe -h localhost -p 5432 -U sa -v -f "D:\workspace\JavaProject\project2\db\bkserver.backup.sql"
--==> Sẽ backup từng database và toàn bộ thông tin liên quan đến SERVER POSTGRESQL.

--BK DB [test];
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -b -D -v -f "D:\workspace\JavaProject\project2\db\test_db.backup" test

--BK SCHEMA [store]
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -D -v -f "D:\workspace\JavaProject\project2\db\test_db.store_schema.backup" -n \"store\" test

--BK TABLE [Customer];
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -D -v -f "D:\workspace\JavaProject\project2\db\test_db.store_schema.Customer_table.backup" -t "\"store\".\"Customer\"" test


--NOTES:
/*BACKUP bằng câu lệnh [pg_dump] hay dùng tool [pgAdmin III] cho từng database/schema/table không ảnh hưởng đến "LOGIN ROLES" hay "GROUP ROLES" đã được tạo trong POSTGRESQL system. Để backup toàn bộ POSTRESQL DATABASE SYSTEM, dùng [pg_dumpall]. Câu lệnh này sẽ phục hồi toàn bộ PostgreSQL system, bao gồm LOGIN và GROUP ROLES.
*/

--p144/402
--Thực thi file script;
#psql -f <sql script file> <db name> <username>
#psql -f "D:\workspace\JavaProject\project2\db\Customer-select.sql" test fred

#psql -U fred -l--Danh sach cua tat ca cac DB duoc tao tren serrver

--11:09 A Sat/13/09/2008
The PSQL META-COMMANDS--p146/402
/*psql meta-commands là loại câu lệnh có dấu "\" đứng trước.
Có thể tìm hiểu các câu lệnh kiểu này bằng keyword [\?]
Hiển thị thong tin vè psql system.
*/
--Danh sách các câu lệnh meta-commands:

Command					Description
\c <dbname>				Connect to different database
\cd <dir>				changing to different working directory on the local system

\copyright				Display the POSTGRESQL copyright info
\encoding <encoding>	Display/SET the current psql encoding
\h <statement>			Display help on a SQL statement
\q						Exit/Quit psql
\set <name value>		Set variable value (same as -v comamnd-line option)
\timing					Display total time a command takes
\unet <name>			Unset variable value
\! <command>				Execute command on HOST system
....Còn rất nhiều thông tin có ích khi đọc phần này. Tham khảo thêm.



--Chapter 6: USING BASIC SQL.--p159/402
--CREATING OBJECTS

--1. CREATE GROUP ROLES. Không cần pasword
-- Role: "SA"
-- DROP ROLE "SA";
CREATE ROLE "SA"
  SUPERUSER INHERIT CREATEDB CREATEROLE;
COMMENT ON ROLE "SA" IS 'sa';


--2. CREATE LOGIN ROLES. Tương tự tạo user/pass
-- Role: "fred"
-- DROP ROLE fred;
CREATE ROLE fred LOGIN
  ENCRYPTED PASSWORD 'md56d49822a4a97b7afed9fe9d21c7005f5'--654321
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT "Accountant" TO fred;
COMMENT ON ROLE fred IS 'Salesman group roles';


--3. Creating DATABASE:
CREATE DATABASE <name> [
	WITH
	[OWNER <owner>]
	[TEMPLATE <template>]
	[ENCODING <encoding>]
	[TABLESPACE <tablespace>]
	[CONNECTIONLIMIT <connlimit>]
]
#\l --listing of database

--VD:
-- Database: test
-- DROP DATABASE test;
CREATE DATABASE test
  WITH OWNER = postgres
       ENCODING = 'UTF8';
COMMENT ON DATABASE test IS 'DB APPLICATION';


--4. Creating SCHEMA:
CREATE SCHEMA <schema name> [
	AUTHORIZATION <username> [schema elements]
]
#\dn --listing of schema
--VD:
-- Schema: "store"
-- DROP SCHEMA store;
CREATE SCHEMA store
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA store TO postgres;
GRANT USAGE ON SCHEMA store TO "Accountant";
GRANT USAGE ON SCHEMA store TO "Salesman";
GRANT ALL ON SCHEMA store TO sa WITH GRANT OPTION;
COMMENT ON SCHEMA store IS 'FIRST SCHEMA';
#\dt store.--listing object on schema [store]
#\d store."Customer" --listing table detail(tên trường, kiểu dữ liệu)

--4.1. Drop schema;
DROP SCHEMA <SCHEMA NAME> [
	CASCADE | RESTRICT
]
--RESTRICT: chưa xoá object trong schema (table, view, trigger) thì không xoá được.-->CHỉ xoá được SCHEMA rỗng
--CASCADE: xoá tất cả các object đã tồn tại mà không hỏi gì

--5. Creating table
\#dt <schema name>."<table name>"
\#d <schema name>."<table name>"
--6. Adding additional table elements;
ALTER TABLE <table name> <action>
========================================
ALTER Action 			Description
========================================
ADD COLUMN
columnname				Add a new column to the table.

DROP COLUMN
columnname				Remove an existing column from the table.
ALTER COLUMN
columnname action		Change the elements of an existing column. Can be
						used to change data type, add keys, or set constraints.
SET DEFAULT value 		Set a default value for an existing column.
DROP DEFAULT 			Remove a defined default value of an existing column.
SET NOT NULL 			Define the NOT NULL constraint on an existing column.
DROP NOT NULL 			Remove a NOT NULL constraint from an existing column.
SET STATISTICS 			Enable statistic gathering used by the ANALYZE command.
SET STORAGE 				Define the storage method used to store the column data.
ADD constraint 			Add a new constraint to the table.
DROP constraint 			Remove a constraint from the table.
DISABLE TRIGGER 			Disable (but not remove) a trigger defined for the table.
ENABLE TRIGGER 			Define a new trigger for the table.
OWNER loginrole 		Set the table owner.
SET TABLESPACE
newspace				Change the tablespace where the table is stored to newspace.
SET SCHEMA newschema 	Change the schema location of the table to newschema.
RENAME COLUMN
oldname TO newname		Change the name of table column oldname to newname.
RENAME TO newname 		Change the name of the table to newname.

--VD:
ALTER TABLE store."Customer" alter column "Phone" set not null;--Thay doi thuoc tinh cua Column
ALTER TABLE store."Customer" alter column "FirstName" set not null;--Thay doi thuoc tinh cua Column
ALTER TABLE employee RENAME TO employee1;--Doi ten table
ALTER TABLE EMPLOYEE ADD COLUMN BIRTHDAY DATE--Them 1 cot
ALTER TABLE EMPLOYEE RENAME COLUMN BIRTHDAY TO BDAY--rename cot
ALTER TABLE EMPLOYEE ALTER COLUMN BDAY SET NOT NULL--rename cot

CREATE TABLE store."Employee"
(
   "EmployeeID" integer NOT NULL, 
   "LastName" character varying(50), 
   "FirstName" character varying(50), 
   "Department" character(5) NOT NULL, 
   "StartDate" date DEFAULT now(), 
   "Salary" money, 
   "Birthday" date NOT NULL, 
   CONSTRAINT "Employee_PK" PRIMARY KEY ("EmployeeID") USING INDEX TABLESPACE pg_default
) WITH (OIDS=FALSE)
TABLESPACE pg_default;
ALTER TABLE store."Employee" OWNER TO sa;
COMMENT ON TABLE store."Employee" IS 'Employee table';


--7. Creating Group/Login ROLES: 
--p172/402
--Group Rolse: controlling access privileges to database objects.
--Login Roles: as a user accounts that can log into the system.
--Login Roles as assigned as members of the appropriate Group Roles to obtain the necessary privileges
CREATE ROLE <ROLE NAME> [
	[WITH] <OPTIONS
]
=======================================
OPTION:					DESCRIPTION
=======================================
ADMIN rolelist 			Add one or more roles as administrative members of the new role.

CONNECTION
LIMIT ‘value’			Limit the number of connections the role can have to the database.
						The default is -1, which is unlimited connections.
						
CREATEDB 				Allow the role to create databases on the system.
CREATEROLE 				Allow the role to create new roles on the system.
ENCRYPTED 				Encrypt the role password within the PostgreSQL system tables.
IN ROLE rolelist 		List one or more roles that the new role will be a member of.
INHERIT 				Specify that the role will inherit all of the privileges of roles it is a
						member of.
						
LOGIN 					Specify that the role can be used to log into the system (a Login Role).
NOLOGIN 				Specify that the role cannot be used to log into the system (a Group
						Role).
PASSWORD passwd 		Specify the password for the role.
ROLE rolelist 			List one or more roles that will be added as members to the new role.
SUPERUSER 				Specify that the new role will have superuser privileges. Only the
superuser 				can use this option.
VALID UNTIL ‘date’ 		Specify a date when the role will expire.


CREATE ROLE command uses NOLOGIN option to create a Group Roles;


--VD:
CREATE ROLE management WITH NOLOGIN;--Group Roles
CREATE ROLE technician NOLOGIN;--Group Roles

CREATE ROLE wilma IN ROLE management;--Login Roles
ALTER ROLE wilma LOGIN PASSWORD 'PEBBLES' INHERIT;--Set passsword, quyen cho login roles
--INHERIT parameter chỉ ra rằng login role này sẽ thừa kế mọi quyền của Group ROles mà nó thuộc về.

CREATE ROLE betty IN ROLE technician;
ALTER ROLE betty LOGIN PASSWORD 'BAMBAM' INHERIT;

#\du wilma
--Sau khi tao role roi den phan xoa ROLES:
--7.1. DROP ROLE <role name>
--NOTES: POSTGRESQL will not let you remote a role if it is the owner of any database object (schema, table). You must perform an ALTER command to change the owner of the object to another role.

--8. Assigning Privileges;
--Now that you have some Group/Login Roles created, it is tiem to assign privileges to database objects. In the SQL language, the command to assign privileges is GRANT:
GRANT <privileges name1, privileges name2, ... > ON <object> TO <roles>
--There are two types of GRANT commands, depending on what the "object" specified in the command is:
	GRANTing privileges to database objects.
	GRANTing privileges to role objects.

D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U sa -F c -b -D -v -f "D:\workspace\JavaProject\project2\db\test_db.backup" test
D:\Program Files\PostgreSQL\8.3\bin\pg_restore.exe -h localhost -p 5432 -U sa -d d1 -v "D:\workspace\JavaProject\project2\db\test_db.backup"

--10:35 A Sun/14/09/2008
#select timeofday();
#select version();

--10:30 AM Tuesday, September 16, 2008
--BK DB [postgres] nguyen ban;
D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h localhost -p 5432 -U postgres -F c -b -D -v -f "D:\workspace\project2\db\postgres_db.backup" postgres

--Phuc hoi database
D:\Program Files\PostgreSQL\8.3\bin\pg_restore.exe -h localhost -p 5432 -U postgres -d test -v "D:\workspace\project2\db\test.backup"

--Sau khi phuc hoi database thi mat het quyen gan tren DB do', chay script phuc hoi lai;
--Done!
--5:05 PM Tuesday, September 16, 2008
--http://jdbc.postgresql.org/documentation/81/connect.html

keytool -genkey -keystore mySrvKeystore -keyalg RSA
Enter keystore password:  123456

Re-enter new password: 123456

What is your first and last name?
  [Unknown]:  TINH
What is the name of your organizational unit?
  [Unknown]:  CNTT
What is the name of your organization?
  [Unknown]:  CBV
What is the name of your City or Locality?
  [Unknown]:  HANOI
What is the name of your State or Province?
  [Unknown]:  HANOI
What is the two-letter country code for this unit?
  [Unknown]:  VN
Is CN=TINH, OU=CNTT, O=CBV, L=HANOI, ST=HANOI, C=VN correct?
  [no]:  yes

Enter key password for <mykey>
	(RETURN if same as keystore password):  123456

Re-enter new password: 123456


/* HOW TO USE JAVA _ SSL
tóm tắt cách sử dụng như sau: 
trước tiên bạn tạo ra 1 dùng để xác thực, file này được tạo ra dùng keytool, đây là 1 tool có sẵn trong j2se sdk. 
bạn chạy command line như sau: 
Code:

keytool -genkey -keystore mySrvKeystore -keyalg RSA
sau đó nhập các thông tin theo yêu cầu, khi hoàn tất sẽ nhận đươc keyfile. (ở đây là 'mySrvKeystore') 
giả sử bạn đặt pass là 123456 
hai class echoserver và echoclient bạn complie như bình thường. tuy nhiên khi chạy bạn sử dụng keyfile đã tạo ở trên bằng cách dùng các tham số như sau: 
server 
Code:

java -Djavax.net.ssl.keyStore=mySrvKeystore -Djavax.net.ssl.keyStorePassword=123456 EchoServer
client 
Code:

java -Djavax.net.ssl.trustStore=mySrvKeystore -Djavax.net.ssl.trustStorePassword=123456 EchoClient

command trên sẽ thêm vào jvm property là các keyfile đã tạo ra. 
http://www.hvaonline.net/hvaonline/posts/list/19230.hva
*/



















